from data_access.read_db import execute_sql, execute_select
from data_access.db_conn import engine
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, Column, String, Float, Integer

# 读取CSV文件
csv_file_path = '../../data-source/Data-Copilot/Educations/SchoolDirectoryandInformation/Co-curricular activities (CCAs).csv'


df = pd.read_csv(csv_file_path)

# 定义数据库表的名称
table_name = 'school_ccas'

# 创建表结构（如果尚不存在）
metadata = MetaData()
table = Table(
    table_name, metadata,
    Column('school_name', String(255)),
    Column('school_section', String(255)),
    Column('cca_grouping', String(255)),
    Column('cca_generic_name', String(255)),
    Column('cca_customized_name', String(255)),
)

# 创建表（如果不存在）
metadata.create_all(engine)


# 准备插入数据的SQL语句
insert_query = f"""
INSERT INTO {table_name} (
    school_name, school_section, cca_grouping,cca_generic_name, cca_customized_name
) VALUES ("%s", "%s","%s", "%s", "%s")
"""

# 插入数据
for index, row in df.iterrows():
    sql = insert_query % (
        row['school_name'], row['school_section'],
        row['cca_grouping_desc'], row['cca_generic_name'],row['cca_customized_name']
    )
    execute_sql(sql)

print("Data has been written to the database.")

